/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Pengadaan;

/**
 *
 * @author elfatah
 */
public class DBPengadaan extends DBClass {

    public Pengadaan getPengadaanDetails(String id) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT peng.PENGADAAN_ID, peg.PEGAWAI_ID, peg.USERNAME, sup.SUPPLYER_ID, sup.NAMA, bar.BARANG_ID, bar.KODE_BARANG, peng.JUMLAH_BARANG, peng.TANGGAL, peng.HARGA_SATUAN, peng.JUMLAH_HARGA FROM `pengadaan` as peng, supplyer as sup, pegawai as peg, barang as bar WHERE peng.ID_BARANG = bar.BARANG_ID and peg.PEGAWAI_ID = peng.ID_PEGAWAI and peng.ID_SUPPLYER = sup.SUPPLYER_ID and peng.PENGADAAN_ID=?");
        pstmt.setString(1, id);
        ResultSet rs = pstmt.executeQuery();

        Pengadaan ubean = new Pengadaan();
        while (rs.next()) {
            ubean.setPengadaanId(Integer.parseInt(rs.getString(1)));
            ubean.setNoPegawai(rs.getString(2));
            ubean.setNamaPegawai(rs.getString(3));
            ubean.setNoSupplier(rs.getString(4));
            ubean.setNamaSupplyer(rs.getString(5));
            ubean.setIdBarang(Integer.parseInt(rs.getString(6)));
            ubean.setNamaBarang(rs.getString(7));
            ubean.setJumlahBarang(Integer.parseInt(rs.getString(8)));
            ubean.setTgl(rs.getString(9));
            ubean.setHargaSatuan(Integer.parseInt(rs.getString(10)));
            ubean.setJumlahHarga(rs.getString(11));
        }
        ubean.setAction("Update");
        return ubean;
    }

    public void UpdatePengadaan(Pengadaan bb, String id) throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("UPDATE `pengadaan` SET `ID_PEGAWAI`=?,`ID_SUPPLYER`=?,`ID_BARANG`=?,`JUMLAH_BARANG`=?,`HARGA_SATUAN`=?,`JUMLAH_HARGA`=? WHERE `PENGADAAN_ID`=?");
        //set values to prepared statement object by getting values from bean object
        pstmt.setInt(1, bb.getIdPegawai());
        pstmt.setInt(2, bb.getIdSupplyer());
        pstmt.setInt(3, 1);
        pstmt.setInt(4, bb.getJumlahBarang());

        pstmt.setInt(5, bb.getHargaSatuan());
        pstmt.setString(6, Integer.toString(bb.getJumlahBarang() * bb.getHargaSatuan()));
        pstmt.setString(7, id);
        pstmt.executeUpdate();

    }

    public void deletePengadaan(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("delete from pengadaan where PENGADAAN_ID=?");
        pstmt.setString(1, uname);
        pstmt.executeUpdate();
    }

    public List getPengadaanDetails() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("SELECT peng.PENGADAAN_ID, peg.NAMA, sup.NAMA, bar.KODE_BARANG, peng.JUMLAH_BARANG, peng.TANGGAL, peng.HARGA_SATUAN, peng.JUMLAH_HARGA FROM `pengadaan` as peng, supplyer as sup, pegawai as peg, barang as bar WHERE peng.ID_BARANG = bar.BARANG_ID and peg.PEGAWAI_ID = peng.ID_PEGAWAI and peng.ID_SUPPLYER = sup.SUPPLYER_ID");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Pengadaan ubean = new Pengadaan();
            ubean.setPengadaanId(Integer.parseInt(rs.getString(1)));
            ubean.setNamaPegawai(rs.getString(2));
            ubean.setNamaSupplyer(rs.getString(3));
            ubean.setNamaBarang(rs.getString(4));
            ubean.setJumlahBarang(Integer.parseInt(rs.getString(5)));
            ubean.setTgl(rs.getString(6));
            ubean.setHargaSatuan(Integer.parseInt(rs.getString(7)));
            ubean.setJumlahHarga(rs.getString(8));

            list.add(ubean);

        }
        return list;
    }

    public void insertPengadaan(Pengadaan bb) throws SQLException, ClassNotFoundException {
        try {
            Connection con = createConnection();
            PreparedStatement pstmt = con.prepareStatement("INSERT INTO `pengadaan`(`ID_PEGAWAI`, `ID_SUPPLYER`, `ID_BARANG`, `JUMLAH_BARANG`, `HARGA_SATUAN`, `JUMLAH_HARGA`) VALUES (?,?,?,?,?,?)");
            //set values to prepared statement object by getting values from bean object
            pstmt.setInt(1, bb.getIdPegawai());
            pstmt.setInt(2, bb.getIdSupplyer());
            pstmt.setInt(3, 1);
            pstmt.setInt(4, bb.getJumlahBarang());
            pstmt.setInt(5, bb.getHargaSatuan());
            pstmt.setString(6, Integer.toString(bb.getJumlahBarang() * bb.getHargaSatuan()));
//            pstmt.setString(7, bb.getPekerjaan());
//
//            pstmt.setString(8, bb.getAgama());

            pstmt.executeUpdate();
            PreparedStatement pstmt2 = con.prepareStatement("UPDATE `barang` SET `JUMLAH_STOK`=(`JUMLAH_STOK`+?) WHERE `BARANG_ID`=?");
            pstmt2.setInt(1, bb.getJumlahBarang());
            pstmt2.setInt(2, bb.getIdBarang());
            pstmt2.executeUpdate();

            System.out.println("testing");
            //  return i;
        } catch (Exception e) {
            System.out.println(e);
            // return 0;
        }
    }
}
